import pandas as pd
import numpy as np


#####Pandas统计功能
##基本统计————————————————————————————————————————————————————————————————————————————————————————————————————
##(1)描述性统计
# #Create a Dictionary of series
# d = {'Name':pd.Series(['Tom','James','Ricky','Vin','Steve','Smith','Jack',
#    'Lee','David','Gasper','Betina','Andres']),
#    'Age':pd.Series([25,26,25,23,30,29,23,34,40,30,51,46]),
#    'Rating':pd.Series([4.23,3.24,3.98,2.56,3.20,4.6,3.8,3.78,2.98,4.80,4.10,3.65])
# }
#
# #Create a DataFrame
# df = pd.DataFrame(d)
#
#
# #sum() 返回请求值总和
# print (df.sum())
#
# # axis=1 返回每行的汇总结果
# print (df.sum(1,numeric_only=True))
#
#
# #mean() 返回平均值
# print (df.mean(numeric_only=True))


# ##（2）汇总DataFrame 列数据
# ##————————————————————————————————include 用于传递需要考虑进行摘要的列的必要信息的参数
# Create a Dictionary of series
# d = {'Name':pd.Series(['Tom','James','Ricky','Vin','Steve','Smith','Jack',
#    'Lee','David','Gasper','Betina','Andres']),
#    'Age':pd.Series([25,26,25,23,30,29,23,34,40,30,51,46]),
#    'Rating':pd.Series([4.23,3.24,3.98,2.56,3.20,4.6,3.8,3.78,2.98,4.80,4.10,3.65])
# }
#
# #Create a DataFrame
# df = pd.DataFrame(d)
# print (df.describe())


# #-——————————————————————————————-object
# # Create a Dictionary of series
# d = {'Name':pd.Series(['Tom','James','Ricky','Vin','Steve','Smith','Jack',
#    'Lee','David','Gasper','Betina','Andres']),
#    'Age':pd.Series([25,26,25,23,30,29,23,34,40,30,51,46]),
#    'Rating':pd.Series([4.23,3.24,3.98,2.56,3.20,4.6,3.8,3.78,2.98,4.80,4.10,3.65])
# }
#
# #Create a DataFrame
# df = pd.DataFrame(d)
# print (df.describe(include=['object']))

##————————————————————————————————all
# #Create a Dictionary of series
# d = {'Name':pd.Series(['Tom','James','Ricky','Vin','Steve','Smith','Jack',
#    'Lee','David','Gasper','Betina','Andres']),
#    'Age':pd.Series([25,26,25,23,30,29,23,34,40,30,51,46]),
#    'Rating':pd.Series([4.23,3.24,3.98,2.56,3.20,4.6,3.8,3.78,2.98,4.80,4.10,3.65])
# }
#
# #Create a DataFrame
# df = pd.DataFrame(d)
# print (df. describe(include='all'))



##分组统计————————————————————————————————————————————————————————————————————————————————————————
##GroupBy——————————————————————————————————
# # 创建示例数据
# data = {
#     'name': ['Alice', 'Bob', 'Charlie', 'Alice', 'Bob'],
#     'city': ['New York', 'London', 'Paris', 'New York', 'London'],
#     'sales': [100, 200, 300, 150, 250]
# }
# df = pd.DataFrame(data)
#
# # 按name列进行分组，并计算sales列的总和
# result = df.groupby('name')['sales'].sum()
# print("GroupBy result from pandasdataframe.com:")
# print(result)



##GroupBy添加新列——————————————————————————
###（1）使用 agg () 添加多个汇总列
# # 创建示例数据
# data = {
#     'product': ['A', 'B', 'A', 'B', 'A'],
#     'category': ['X', 'X', 'Y', 'Y', 'X'],
#     'sales': [100, 200, 150, 300, 120],
#     'quantity': [10, 15, 12, 20, 8]
# }
# df = pd.DataFrame(data)
#
# # 使用agg()方法添加多个汇总列
# result = df.groupby('product').agg({
#     'sales': ['sum', 'mean'],
#     'quantity': ['sum', 'max']
# })
#
# print("Aggregation result from pandasdataframe.com:")
# print(result)

##(2)使用 transform( ) 添加组内计算机
# # 创建示例数据
# data = {
#     'team': ['A', 'A', 'B', 'B', 'A'],
#     'player': ['P1', 'P2', 'P3', 'P4', 'P5'],
#     'score': [10, 15, 12, 8, 20]
# }
# df = pd.DataFrame(data)
#
# # 使用transform()添加组内平均分数列
# df['team_avg_score'] = df.groupby('team')['score'].transform('mean')
#
# print("DataFrame with team average score :")
# print(df)


##GroupBy进行列的汇总计算————————————————————————————————————————————————
##（1）使用 sum ( ) 计算列总和
# # 创建示例数据
# data = {
#     'category': ['A', 'B', 'A', 'B', 'A'],
#     'subcategory': ['X', 'X', 'Y', 'Y', 'X'],
#     'sales': [100, 200, 150, 300, 120]
# }
# df = pd.DataFrame(data)
#
# ##使用sum()计算每个类别的总销售额
# result = df.groupby('category')['sales'].sum()
#
# print("Sum of sales by category from pandasdataframe.com:")
# print(result)



##（2）使用 mean( )计算列平均值
# # 创建示例数据
# data = {
#     'product': ['A', 'B', 'A', 'B', 'A'],
#     'store': ['S1', 'S1', 'S2', 'S2', 'S1'],
#     'price': [10, 15, 12, 18, 11]
# }
# df = pd.DataFrame(data)
#
# # 使用mean()计算每个产品的平均价格
# result = df.groupby('product')['price'].mean()
#
# print("Average price by product from pandasdataframe.com:")
# print(result)


##（3）使用 count ( ) 计算组内元素数量
# # 创建示例数据
# data = {
#     'city': ['New York', 'London', 'Paris', 'New York', 'London'],
#     'visitor': ['V1', 'V2', 'V3', 'V4', 'V5']
# }
# df = pd.DataFrame(data)
#
# # 使用count()计算每个城市的访客数量
# result = df.groupby('city')['visitor'].count()
#
# print("Visitor count by city from pandasdataframe.com:")
# print(result)





###高级 GroupBy——————————————————————————————————————————————————————————
## (1)多列分组
# # 创建示例数据
# data = {
#     'year': [2020, 2020, 2021, 2021, 2020],
#     'quarter': [1, 2, 1, 2, 1],
#     'sales': [1000, 1200, 1100, 1300, 1050]
# }
# df = pd.DataFrame(data)
#
# # 按年份和季度分组，计算销售总额
# result = df.groupby(['year', 'quarter'])['sales'].sum()
#
# print("Sales by year and quarter :")
# print(result)



##（2）使用自定义聚合函数
# # 创建示例数据
# data = {
#     'product': ['A', 'B', 'A', 'B', 'A'],
#     'sales': [100, 200, 150, 300, 120]
# }
# df = pd.DataFrame(data)
#
# # 定义自定义聚合函数
# def sales_range(x):
#     return x.max() - x.min()
#
# # 使用自定义函数进行聚合
# result = df.groupby('product')['sales'].agg(['sum', 'mean', sales_range])
#
# print("Custom aggregation result :")
# print(result)


##(3)使用 groupby( ).filter( ) 进行过滤
# # 创建示例数据
# data = {
#     'team': ['A', 'A', 'B', 'B', 'C'],
#     'player': ['P1', 'P2', 'P3', 'P4', 'P5'],
#     'score': [10, 15, 12, 8, 20]
# }
# df = pd.DataFrame(data)
#
# # 过滤出平均分数大于10的团队
# result = df.groupby('team').filter(lambda x: x['score'].mean() > 10)
#
# print("Filtered DataFrame from pandasdataframe.com:")
# print(result)



##(4)使用 groupby( ).transform( )进行组内标准化
# # 创建示例数据
# data = {
#     'department': ['IT', 'HR', 'IT', 'HR', 'IT'],
#     'employee': ['E1', 'E2', 'E3', 'E4', 'E5'],
#     'salary': [5000, 4000, 6000, 4500, 5500]
# }
# df = pd.DataFrame(data)
#
# # 进行组内标准化
# df['salary_normalized'] = df.groupby('department')['salary'].transform(lambda x: (x - x.mean()) / x.std())
#
# print("DataFrame with normalized salary :")
# print(df)


###GroupBy操作性能的优化————————————————————————————————————————————————————————————————
##(1)使用categoricals
# # 创建示例数据
# data = {
#     'category': ['A', 'B', 'A', 'B', 'A'] * 1000,
#     'value': range(5000)
# }
# df = pd.DataFrame(data)
#
# # 将category列转换为categorical类型
# df['category'] = df['category'].astype('category')
# df['value'] = df['value'].astype('float')
#
# # 进行GroupBy操作
# result = df.groupby('category',observed = True)['value'].sum()
#
# print("GroupBy result with categorical :")
# print(result)


# ##(2)使用 numba 加速
# from numba import jit    #将python代码 编译成机器码
# # 创建示例数据
# np.random.seed(0)   #设置随机种子确保结果可重现
# data = {
#     'group': np.random.choice(['A', 'B', 'C'], size=100000), #10万个随机分组标签
#     'value': np.random.randn(100000)   #10万个标准正态分布的随机值
# }
# df = pd.DataFrame(data)
#
# # 使用numba定义加速函数
# @jit(nopython=True)   #使用 nopython 模式获得最佳性能
# def custom_agg(values):
#     return np.mean(values) * np.std(values)  #自定义聚合函数:均值 * 标准差
#
# # 进行GroupBy操作
# result = df.groupby('group')['value'].agg(lambda x: custom_agg(x.values))
#
# print("GroupBy result with numba :")
# print(result)

















